package com.nvwa.easyexcel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.alibaba.excel.write.style.row.SimpleRowHeightStyleStrategy;
import com.google.common.collect.Lists;
import com.nvwa.coding.util.safe.Coding;
import com.nvwa.coding.util.date.LocalDateUtil;
import com.nvwa.dto.query.PageQuery;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.Collections;
import java.util.List;
import java.util.Set;
import java.util.function.Function;

/**
 * easy excel 工具类
 * 使用示例：EasyExcelHelper.output(list, Arrays.asList("注册省份", "人数"), "统计分析-省份", response);
 *
 * 使用示例2：EasyExcelHelper.output(list, User.class, fileName, response, "createTimes");
 * User对象中使用注解@ExcelProperty(value = "姓名", order = 1)标注属性
 *
 * //TODO 优化、增加水印
 */
public class EasyExcelHelper {

    private static final WriteHandler DEFAULT_STYLE_HANDLER;

    static {
        //设置内容区域样式
        WriteCellStyle contentStyle = new WriteCellStyle();
        //设置边框
        contentStyle.setBorderTop(BorderStyle.THIN);
        contentStyle.setBorderBottom(BorderStyle.THIN);
        contentStyle.setBorderLeft(BorderStyle.THIN);
        contentStyle.setBorderRight(BorderStyle.THIN);
        //设置居中
        contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

        //设置表头样式
        WriteCellStyle headStyle = new WriteCellStyle();
        WriteCellStyle.merge(contentStyle, headStyle);
        //设置背景色
        headStyle.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        //设置文本样式
        WriteFont headFont = new WriteFont();
        headFont.setBold(Boolean.TRUE);
        headFont.setFontHeightInPoints((short) 14);

        DEFAULT_STYLE_HANDLER = new HorizontalCellStyleStrategy(headStyle, contentStyle);
    }

    /**
     * 导出excel
     *
     * @param dataList 数据列表
     * @param head     表头（只支持单行表头）
     * @param fileName 文件名（直接传明文名称，本方法会添加时间后缀并进行url编码处理）
     * @param response HttpServletResponse
     * @param <R>      数据类
     * @throws IOException
     */
    public static <R> void export(List<R> dataList, List<String> head, String fileName, HttpServletResponse response)
            throws IOException {
        //设置相应头
        setResponseHead(response, fileName);
        //表头
        List<List<String>> headList = Coding.nonNullThen(head, list -> Lists.partition(list, 1));
        //导出
        EasyExcel.write(response.getOutputStream())
                .registerWriteHandler(DEFAULT_STYLE_HANDLER)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 35, (short) 25))
                .head(headList)
                .sheet()
                .doWrite(dataList);
    }

    /**
     * 导出excel
     *
     * @param dataList      数据列表
     * @param head          表头Class，即设置了字段注解的类
     * @param fileName      文件名（直接传明文名称，本方法会添加时间后缀并进行url转码处理）
     * @param response      HttpServletResponse
     * @param excludeFields 需要排除的字段名
     * @param <R>           数据类
     * @throws IOException
     */
    public static <R> void export(List<R> dataList, Class<R> head, String fileName, HttpServletResponse response,
                                  String... excludeFields)
            throws IOException {
        //设置相应头
        setResponseHead(response, fileName);
        //导出
        try (ExcelWriter writer = getWriter(response, head, excludeFields)) {
            writer.write(dataList, EasyExcel.writerSheet().build());
        }
    }

    /**
     * 分批导出excel，导出数据在同一个sheet中。针对大量数据时使用，可以减少内存占用，提高导出效率，批次大小为 2000
     *
     * @param queryFunction 查询数据列表方法
     * @param pageQuery    查询参数
     * @param total         记录总数量
     * @param head          表头Class，即设置了字段注解的类
     * @param fileName      文件名（直接传明文名称，本方法会添加时间后缀并进行url转码处理）
     * @param response      HttpServletResponse
     * @param excludeFields 需要排除的字段名
     * @param <T>           查询参数类
     * @param <R>           数据类
     * @throws IOException
     */
    public static <T extends PageQuery, R> void outputBatching(Function<T, List<R>> queryFunction, T pageQuery,
                                                               int total, Class<R> head, String fileName, HttpServletResponse response, String... excludeFields)
            throws IOException {
        //设置响应头
        setResponseHead(response, fileName);
        //分批导出数据
        //获取excel写入器
        try (ExcelWriter writer = getWriter(response, head, excludeFields)) {
            //创建sheet
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            if (total <= 0) {
                //无数据，创建只有表头的excel
                writer.write(Collections.emptyList(), writeSheet);
                return;
            }
            //设置每次查询数量
            pageQuery.setPageSize(2000);
            //计算总页数
            int pageTotal = (int) Math.ceil((double) total / pageQuery.getPageSize());
            for (int pageNo = 1; pageNo <= pageTotal; pageNo++) {
                pageQuery.setPageNo(pageNo);
                //查询数据
                List<R> dataList = queryFunction.apply(pageQuery);
                //写入数据
                writer.write(dataList, writeSheet);
            }
        }
    }

    private static <R> ExcelWriter getWriter(HttpServletResponse response, Class<R> head, String... excludeFields)
            throws IOException {
        Set<String> excludeFieldSet = Coding.hashSet(excludeFields);
        return EasyExcel.write(response.getOutputStream())
                .excludeColumnFieldNames(excludeFieldSet)
                .registerWriteHandler(DEFAULT_STYLE_HANDLER)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 35, (short) 25))
                .head(head)
                .build();
    }

    private static void setResponseHead(HttpServletResponse response, String fileName)
            throws UnsupportedEncodingException {
        //设置相应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        String charset = StandardCharsets.UTF_8.name();
        response.setCharacterEncoding(charset);
        //文件名添加时间及随机数后缀
        fileName = String.format("%s-%s", fileName,
                LocalDateUtil.getLocalDateTimeStr(LocalDateUtil.UNSIGNED_DATETIME_PATTERN));
        //url转码处理
        fileName = URLEncoder.encode(fileName, charset).replace("+", "%20");
        response.setHeader("Content-Disposition",
                String.format("attachment;filename*=%s''%s.xlsx", charset.toLowerCase(), fileName));
    }

}
